package com.spottydog.virtues;

import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import com.spottydog.virtues.VirtueDate.Virtue;

public class VirtueDatabase {
	
	private final VirtueDatabaseOpenHelper databaseOpenHelper;

	public VirtueDatabase(Context context) {
		databaseOpenHelper = new VirtueDatabaseOpenHelper(context);
	}
	
	public List<VirtueDate> getForDate(Date date) {		
		List<VirtueDate> result = new ArrayList<VirtueDate>();
		
		String sql = "SELECT ROWID, virtue, count, vals FROM virtue WHERE vdate = " + Utils.toUnixtime(date);
		Cursor c = databaseOpenHelper.getReadableDatabase().rawQuery(sql, new String[]{});
		
		Map<Virtue, VirtueDate> lookup = new HashMap<Virtue, VirtueDate>();
		
		if (c.moveToFirst()) do {
			VirtueDate v = new VirtueDate(c.getInt(0), c.getInt(2), c.getString(3), date, Virtue.valueOf(c.getString(1)));
			lookup.put(v.getVirtue(), v);
		} while (c.moveToNext());
		
		c.close();
		
		for (Virtue v : VirtueDate.Virtue.values()) {
			if (lookup.get(v) != null) result.add(lookup.get(v));
			else result.add(new VirtueDate(0, "", date, v));
		}
		
		return result;
	}
	
	public void save(VirtueDate vd) {
		String sql = "";
		boolean insert = false;
		if (vd.getRowId() != -1) {
			sql = "UPDATE virtue SET count = " + vd.getCount() + ", vals = \"" + vd.getValues() + "\" WHERE ROWID = " + vd.getRowId();
		} else {
			insert = true;
			sql = "INSERT INTO virtue (virtue, vdate, count, vals) VALUES (" +
				"\"" + vd.getVirtue().name() + "\", " +
				Utils.toUnixtime(vd.getDate()) + ", " +
				vd.getCount() + ", " +
				"\"" + vd.getValues() + "\"" + ")";
		}
		
		databaseOpenHelper.getWritableDatabase().execSQL(sql);
		
		if (insert) {
			Cursor c = databaseOpenHelper.getReadableDatabase().rawQuery("SELECT last_insert_rowid()", new String[]{});
			if (c.moveToFirst()) do {
				vd.setRowId(c.getInt(0));
			} while (c.moveToNext());
			
			c.close();
		}
	}
	
	private class VirtueDatabaseOpenHelper extends SQLiteOpenHelper {
		
		public VirtueDatabaseOpenHelper(Context context) {
			super(context, "virtue", null, 2);
		}
		
		@Override
		public void onCreate(SQLiteDatabase db) {
			db.execSQL("CREATE TABLE virtue (" +
					"virtue VARCHAR(255)," +
					"vdate INTEGER," +
					"count INTEGER," +
					"vals VARCHAR(255))");
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			return; // Nothing to do for v1
		}
	}
}
